﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using CommonClasses;

namespace QCCM2
{

    public class MaterialQCAttrib
    {
        private string materialName;
        private int materialID;
        private string qcAttribName;
        private int qcAttribID;
        private string measurementType;
        private decimal target;
        private decimal threshold;
        private decimal upper;
        private decimal lower;
        private bool materialQCADisabled;


        public string MaterialName
        {
            get { return materialName; }
            set { materialName = value; }
        }


        public int MaterialID
        {
            get { return materialID; }
            set { materialID = value; }
        }


        public string QCAttribName
        {
            get { return qcAttribName; }
            set { qcAttribName = value; }
        }

        public int QCAttribID
        {
            get { return qcAttribID;}
            set { qcAttribID = value;}
        }

        public string MeasurementType
        {
            get { return measurementType; }
            set { measurementType = value; }
        }

        public decimal Target
        {
            get { return target;}
            set { target = value;}
        }

        public decimal Threshold
        {
            get { return threshold;}
            set { threshold = value;}
        }

        public decimal Upper
        {
            get { return upper;}
            set { upper = value;}
        }

        public decimal Lower
        {
            get { return lower; }
            set { lower = value; }
        }

        public bool MaterialQCADisabled
        {
            get { return materialQCADisabled;}
            set { materialQCADisabled = value;}
        }

        Log LogUpdate = new Log();


        String connectionString = @"Data Source=t-sql;Initial Catalog=QCConfigurationManager;Integrated Security=True";

        public List<MaterialQCAttrib> GetAllMaterialQCAttrib()
        {
            List<MaterialQCAttrib> MaterialQCAttribList = new List<MaterialQCAttrib>();


            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                string queryString = "SELECT  m.MaterialName, m.MaterialID, qca.QCAttribName, qca.QCAttribID, mca.[Target], mca.Threshold, mca.[Upper], mca.[Lower],  mca.MaterialQCAttribDisabled, mca.MeasurementType FROM Material as m, QCAttrib as qca, MaterialQCAttrib as mca WHERE mca.MaterialID=m.MaterialID and mca.QCAttribID=qca.QCAttribID";
                    
                try
                {
  
                    SqlCommand command = new SqlCommand(queryString, conn);
                    conn.Open();
                    SqlDataReader reader = command.ExecuteReader();
                   
                    while (reader.Read())
                    {
                        MaterialQCAttrib mqa = new MaterialQCAttrib();

                        mqa.materialName = reader.GetString(0);
                        mqa.materialID = reader.GetInt32(1);
                        mqa.qcAttribName = reader.GetString(2);
                        mqa.qcAttribID = reader.GetInt32(3);
                        mqa.target = reader.GetDecimal(4);
                        mqa.threshold = reader.GetDecimal(5);
                        mqa.upper = reader.GetDecimal(6);
                        mqa.lower = reader.GetDecimal(7);
                        mqa.materialQCADisabled = reader.GetBoolean(8);
                        mqa.measurementType = reader.GetString(9);   

                        MaterialQCAttribList.Add(mqa);
                    }
                    
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.ToString());
                }

                return MaterialQCAttribList;
            }
        }

        public bool AddMaterialQCAttrib(MaterialQCAttrib obj)
        {
            bool success = false;

            try
            {

                using (SqlConnection conn = new SqlConnection(connectionString))
                {

                    string sqlString = "insert into [dbo].[MaterialQCAttrib](materialName, materialID, qcAttribName, qcAttribID, target, threshold, upper, lower, materialQCAttribDisabled, measurementType) values(@MaterialName, @MaterialID, @QCAttribName, @QCAttribID, @Target, @Threshold, @Upper, @Lower, @MaterialQCAttribDisabled, @MeasurementType)";
                    conn.Open();
                    using (SqlCommand command = new SqlCommand(sqlString, conn))
                    {
                        command.Parameters.AddWithValue("@MaterialName", obj.MaterialName);
                        command.Parameters.AddWithValue("@MaterialID", obj.MaterialID);
                        command.Parameters.AddWithValue("@QCAttribName", obj.QCAttribName);
                        command.Parameters.AddWithValue("@QCAttribID", obj.QCAttribID);                      
                        command.Parameters.AddWithValue("@Target", obj.Target);
                        command.Parameters.AddWithValue("@Threshold", obj.Threshold);
                        command.Parameters.AddWithValue("@Upper", obj.Upper);
                        command.Parameters.AddWithValue("@Lower", obj.Lower);
                        command.Parameters.AddWithValue("@MaterialQCAttribDisabled", obj.materialQCADisabled);
                        command.Parameters.AddWithValue("@MeasurementType", obj.MeasurementType);
                        
                        int rows = command.ExecuteNonQuery();

                        if (rows == 1)
                        {
                            success = true;
                        }

                        //rows number of record got updated
                    }
                }
            }
            catch (SqlException ex)
            {
                //Log exception
                //Display Error message
                Console.WriteLine(ex.ToString());
            }
            LogUpdate.LogTransaction(0, "QC Material Attribute has been Successfully Added", "", "Info");
            return success;
        }

        public bool UpdateMaterialQCAttrib(MaterialQCAttrib obj)
        
        {           
            try
            {
                                   
                using (SqlConnection conn =  new SqlConnection(connectionString))
                {
                    string sqlString = "UPDATE [dbo].[MaterialQCAttrib] SET materialName= @MaterialName, materialID= @MaterialID, qcAttribName= @QCAttribName, qcAttribID= @QCAttribID, target =@Target, threshold=@Threshold, upper=@Upper, lower=@Lower, materialQCAttribDisabled=@MaterialQCAttribDisabled, measurementType=@MeasurementType  WHERE MaterialName = '" + obj.MaterialName + "' And QCAttribName = '" + obj.QCAttribName + "'";
                    conn.Open();
                    using (SqlCommand command = new SqlCommand(sqlString, conn))                    
                    {
                        command.Parameters.AddWithValue("@MaterialName", obj.MaterialName);
                        command.Parameters.AddWithValue("@MaterialID", obj.MaterialID);
                        command.Parameters.AddWithValue("@QCAttribName", obj.QCAttribName);
                        command.Parameters.AddWithValue("@QCAttribID", obj.QCAttribID);     
                        command.Parameters.AddWithValue("@Target", obj.Target); 
                        command.Parameters.AddWithValue("@Threshold", obj.Threshold);
                        command.Parameters.AddWithValue("@Upper", obj.Upper);
                        command.Parameters.AddWithValue("@Lower", obj.Lower);
                        command.Parameters.AddWithValue("@MaterialQCAttribDisabled", obj.MaterialQCADisabled);
                        command.Parameters.AddWithValue("@MeasurementType", obj.MeasurementType);
        
                        int rows = command.ExecuteNonQuery();
                        return true;
                        //rows number of record got updated
                    }
                }
            }
            catch (SqlException ex)
            {
                //Log exception
                //Display Error message
                Console.WriteLine(ex.ToString());
            }
            LogUpdate.LogTransaction(0, "QC Material Attribute has been Successfully Updated", "", "Info");
                return true;
        }

        public bool DeleteMaterialQCAttrib(MaterialQCAttrib obj)
        {
            string result = "";
           try
            {

                using (SqlConnection conn = new SqlConnection(connectionString))
                {
         
                string sqlString = "DELETE FROM [dbo].[MaterialQCAttrib] WHERE MaterialID=@MaterialID AND QCAttribID=@QCAttribID";
                    conn.Open();
                    using (SqlCommand command = new SqlCommand(sqlString, conn))
                    {
                        command.Parameters.AddWithValue("@MaterialID", obj.MaterialID);
                        command.Parameters.AddWithValue("@QCAttribID", obj.QCAttribID);

                        int rows = command.ExecuteNonQuery();
                        return true;
                        //rows number of record got deleted
                    }
                }
            }
           catch (FileNotFoundException fnfe)
           {
               result = fnfe.Message;
           }
            catch (SqlException ex)
            {
                //Log exception
                //Display Error message
                Console.WriteLine(ex.ToString());
            }

           LogUpdate.LogTransaction(0, "QC Material Attribute has been Successfully Deleted", "", "Info");
            return true;

        }             

    }
}
        

        









